const { Op, Sequelize } = require("sequelize");
const ArticleCount = require('../model/article_count.model');
const moment = require('moment');
const seq = require('../db/seq')
class pvService {
    // 访问量 
    /**
     * 如果当日内没存过,则创建一条，如果当日存过在原有基础上增加个数
     * */
    async saveViewService(param) {
        const { id, user_id } = param;
        const where = {
            id: { [Op.eq]: id }
        }

        let createdAtFrom = moment(new Date()).format('YYYY-MM-DD 00:00:00')
        let createdAtTo = moment(new Date()).format('YYYY-MM-DD 23:59:59')
        let date = moment(new Date()).format('YYYY-MM-DD')
        where['createdAt'] = {
            [Op.between]: [createdAtFrom, createdAtTo]
        }
        const res = await ArticleCount.findOne({
            where
        })
        // 没有查到
        if (!res) {
            const data = await ArticleCount.create({ id, user_id, PV: 1, date })
            return 'success'
        } else {
            // 查到之后更改
            const data = await ArticleCount.update({ PV: Number(res.dataValues.PV) + 1 }, {
                where: { id, createdAt: { [Op.between]: [createdAtFrom, createdAtTo] } }
            })
            return 'success'
        }

    }
    /**
     * 查询单篇文章的访问量PV
     * @param {id} 文章id 
     * @returns 数据库返回 [ { PV: 4 } ]
     */
    async getOnePagePvService(param) {
        const { id } = param;
        const where = {
            id: { [Op.eq]: id }
        }
        // 用到了 SUM 和 literal
        try {
            const res = await ArticleCount.findAll({
                where,
                // 后一个参数 'PV' 是重命名  必须使用[[]]两层
                attributes: [[Sequelize.literal('SUM(PV)'), 'PV']],
                raw: true     // raw 对查询结果进行格式化， false 返回 instance
            })
            console.log(res)
            return res[0]
        } catch (err) {
            console.log(err)
            return err
        }
    }
    /**
     * 根据user_id获得其所有文章的PV
     * 
     * 
     */
    async getAuthorPvService(param) {
        const { user_id } = param;
        // 用到了 SUM 和 literal
        try {
            const res = await ArticleCount.findAll({
                attributes: [
                    // 自定义sql
                    [Sequelize.literal(`( SELECT SUM(PV) From  ArticleCounts WHERE user_id = ${user_id})`), 'count']
                ],


            })
            console.log(res)
            return res[0]
        } catch (err) {
            console.log(err)
            return err
        }
    }
    /**
     * 
     * 作者的7 15 30天访问量 
     * 
     * 
     */
    async getDateAuthorPvService(param) {
        const { user_id, dateFrom, dateTo } = param;

        let result;
        console.log(11111111, user_id, dateFrom, dateTo)
        try {
            //   seq是定义的数据库实例
            await seq.query(`(SELECT Sum(Pv),date FROM ArticleCounts WHERE user_id=${user_id} AND date BETWEEN '${dateFrom}' AND '${dateTo}' GROUP BY date)`, { type: seq.QueryTypes.SELECT }).then((res) => {
                //  return 不能写到promise中
                result = res
            })
            return result
        } catch (err) {
            console.log(err)
            return err
        }
    }
    /**
     * 
     * 文章的7 15 30天访问量 
     * @returns   [{"Sum(Pv)": 11,"date": "2022-03-03"}]
     */
    async getDateArticlePvService(param) {
        console.log(11)
        const { id, dateFrom, dateTo } = param;
        let result;
        try {
            //   seq是定义的数据库实例  必须加await
            await seq.query(`(SELECT Sum(Pv) as count,date FROM ArticleCounts WHERE id=${id} AND date BETWEEN '${dateFrom}' AND '${dateTo}' GROUP BY date)`, { type: seq.QueryTypes.SELECT }).then((res) => {
                result = res
                console.log(res)
            })
            return result

        } catch (err) {
            console.log(err)
            return err
        }
    }
    // 管理端使用
    async getPvSumSevice() {
        const fromDate = moment().subtract(7, 'days').format('YYYY-MM-DD')
        const toDate = moment().format('YYYY-MM-DD')

        console.log(fromDate, toDate)
        let result;
        try {
            //   seq是定义的数据库实例  必须加await
            await seq.query(`(SELECT Sum(Pv) as count,date FROM ArticleCounts WHERE date BETWEEN '${fromDate}' AND '${toDate}' GROUP BY date)`, { type: seq.QueryTypes.SELECT }).then((res) => {
                result = res
                console.log(res)
            })
            return result

        } catch (err) {
            console.log(err)
            return err
        }
    }


    async articleCountService() {
        let result;
        try {
            //   seq是定义的数据库实例  必须加await
            await seq.query(`(SELECT count(id) as count,type FROM Articles GROUP BY type)`, { type: seq.QueryTypes.SELECT }).then((res) => {
                result = res
                console.log(res)
            })
            return result

        } catch (err) {
            console.log(err)
            return err
        }
    }

    async getWordCloudService() {
        let result
        try {
            //   seq是定义的数据库实例  必须加await
            await seq.query(`(SELECT COUNT(keyword)AS count,keyword FROM KeyWords GROUP BY keyword)`, { type: seq.QueryTypes.SELECT }).then((res) => {
                result = res
                console.log(res)
            })
            return result

        } catch (err) {
            console.log(err)
            return err
        }
    }
}
// 导出后 在控制器controller中使用
module.exports = new pvService()